package com.webull.information.center.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.webull.framework.mvc.ApiException;
import com.webull.framework.util.UtilJdbc;
import com.webull.information.center.common.util.MessageSender;
import com.webull.information.client.model.News;
import com.webull.information.client.model.NewsLabel;
import com.webull.information.client.model.NewsLabelExt;
import com.webull.information.client.model.parm.NewsParam;
import com.webull.information.client.model.parm.NewsUpdateEvent;

/**
 * 新闻Dao
 *
 * @author shimingjun
 * @version 1.0
 * @date 2016年6月2日 下午5:08:52
 * @since JDK 1.8
 */
@Repository
public class InfoNewsDao {
    private static final RowMapper<News> newsRM = BeanPropertyRowMapper.newInstance(News.class);
    private static final RowMapper<NewsLabel> newsLableRM = BeanPropertyRowMapper.newInstance(NewsLabel.class);
    private static final RowMapper<NewsLabelExt> newsLableExRM = BeanPropertyRowMapper.newInstance(NewsLabelExt.class);
    protected final Logger logger = LogManager.getLogger(getClass());

    private @Autowired
    JdbcTemplate jdbcTemplate;
    private @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private @Autowired
    MessageSender messageSender;
    private @Autowired
    DbShardHelper dbShardHelper;

    /**
     * 新增新闻
     *
     * @param news
     * @return
     * @since 0.1.0
     */
    public Integer addNews(News news) {

        Long nextNewsId = dbShardHelper.nextNewsId();
        news.setId(nextNewsId);
        namedParameterJdbcTemplate.update(dbShardHelper.shardDealSql(Sql.ADD_TICKER_NEWS, nextNewsId),
                JdbcHelper.getSqlParameterSource(news));

        return nextNewsId.intValue();
    }

    /**
     * @param tickerId
     * @param newsId
     * @param tickerType
     * @return
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public int addTickerNewsRel(Integer tickerId, Integer newsId, Integer tickerType) {
        if (!existTickerNewsRel(tickerId, newsId)) {
            Map parm = new HashMap();
            parm.put("newsId", newsId);
            parm.put("tickerId", tickerId);
            parm.put("tickerType", tickerType);
            namedParameterJdbcTemplate.update(dbShardHelper.shardDealSql(Sql.ADD_TICKER_NEWS_REL, newsId.longValue()),
                    parm);
            return 1;
        }
        return 0;
    }

    public boolean existTickerNewsRel(Integer tickerId, Integer newsId) {
        Map parm = new HashMap();
        parm.put("newsId", newsId);
        parm.put("tickerId", tickerId);
        Integer count = namedParameterJdbcTemplate.queryForObject(
                dbShardHelper.shardDealSql(Sql.exist_TICKER_NEWS_REL, newsId.longValue()), //
                parm, Integer.class);
        return count > 0;
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public int addCurrencyNewsRel(Integer currencyId, Integer newsId) {
        /*
         * non shard
		 */
        if (!existCurrencyNewsRel(currencyId, newsId)) {
            Map parm = new HashMap();
            parm.put("newsId", newsId);
            parm.put("currencyId", currencyId);
            namedParameterJdbcTemplate.update(Sql.ADD_currency_NEWS_REL, parm);
            return 1;
        }
        return 0;
    }

    public boolean existCurrencyNewsRel(Integer currencyId, Integer newsId) {
        Map parm = new HashMap();
        parm.put("newsId", newsId);
        parm.put("currencyId", currencyId);
        Integer count = namedParameterJdbcTemplate.queryForObject(Sql.exist_currency_NEWS_REL, parm, Integer.class);
        return count > 0;
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public int addFutureCatNewsRel(Integer categoryId, Integer newsId) {
        if (!existFutureCatNewsRel(categoryId, newsId)) {
            Map parm = new HashMap();
            parm.put("newsId", newsId);
            parm.put("futureCatId", categoryId);
            namedParameterJdbcTemplate.update(
                    dbShardHelper.shardDealSql(Sql.ADD_futureCat_NEWS_REL, newsId.longValue()), //
                    parm);
            return 1;
        }
        return 0;
    }

    public boolean existFutureCatNewsRel(Integer categoryId, Integer newsId) {
        Map parm = new HashMap();
        parm.put("newsId", newsId);
        parm.put("futureCatId", categoryId);
        Integer count = namedParameterJdbcTemplate.queryForObject(
                dbShardHelper.shardDealSql(Sql.exist_futureCat_NEWS_REL, newsId.longValue()), //
                parm, Integer.class);
        return count > 0;
    }

    /**
     * 根据id查找新闻
     *
     * @param id 新闻id
     * @return 对应的新闻对象
     */
    public News getById(Long id) {
        return UtilJdbc.queryForObject(jdbcTemplate, //
                dbShardHelper.shardDealSql(Sql.GETBYID, id), newsRM, id);
    }

    /**
     * 根据新闻ID删除
     *
     * @param id
     * @return
     * @since 0.1.0
     */
    public void delById(Long id) {
        jdbcTemplate.update(dbShardHelper.shardDealSql(Sql.DELBYID, id), //
                id);
    }

    /**
     * 根据tickerId获取关联新闻ID列表(提供分页功能，根据最后坐标,依次往后返回指定数量数据)
     *
     * @param tickerId
     * @param currentNewsId
     * @param pageSize
     * @return
     * @author shimingjun
     * @since 0.1.0
     */

    @SuppressWarnings({"unchecked", "rawtypes"})
    public List<News> searchByTickerId(Integer tickerId, Long currentNewsId, Integer pageSize, String lang) {
        List parm = new ArrayList(5);
        String sql = Sql.NEWSBYTICKERID_1;
        parm.add(tickerId);
        parm.add(lang);
        if (currentNewsId != null && currentNewsId > 0) {// 开启分页模式
            sql += Sql.NEWSBYTICKERID_2;
            parm.add(currentNewsId);
            parm.add(currentNewsId);
            parm.add(currentNewsId);
        }
        sql += Sql.NEWSBYTICKERID_3;
        parm.add(pageSize);
        return jdbcTemplate.query(dbShardHelper.shardReleaseSql(sql), // 最新shard
                parm.toArray(), newsRM);
    }

    /**
     * 根据tickerid列表获取新闻列表
     *
     * @param tickerIds
     * @param currentNewsId
     * @param pageSize
     * @return
     * @since 0.1.0
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    public List<News> searchByTickerIds(Integer[] tickerIds, Long currentNewsId, Integer pageSize, String lang) {
        List parm = new ArrayList(5);

        String sq_tickerIds = "";
        for (int i = 0; i < tickerIds.length; i++) {
            sq_tickerIds += "'" + tickerIds[i] + "'" + ",";
        }
        sq_tickerIds = StringUtils.substringBeforeLast(sq_tickerIds, ",");
        String sql = StringUtils.replaceOnce(Sql.NEWSBYTICKERIDS_1, "$", sq_tickerIds);

        parm.add(lang);
        if (currentNewsId != null && currentNewsId > 0) {// 开启分页模式
            sql += Sql.NEWSBYTICKERID_2;
            parm.add(currentNewsId);
            parm.add(currentNewsId);
            parm.add(currentNewsId);
        }
        sql += Sql.NEWSBYTICKERID_3;
        parm.add(pageSize);
        return jdbcTemplate.query(dbShardHelper.shardReleaseSql(sql), // 最新shard
                parm.toArray(), newsRM);
    }

    /**
     * 根据RegionIds获取关联新闻ID列表(提供分页功能，根据最后坐标,依次往后返回指定数量数据)
     *
     * @param currentNewsId
     * @param pageSize
     * @return
     * @author shimingjun
     * @since 0.1.0
     */
    public List<News> searchHomeNewsByLang(Long currentNewsId, Integer pageSize, String lang) {
        //

        String sql = Sql.NEWSBYREGIONIDS_1;
        if (currentNewsId != null && currentNewsId > 0) {// 开启分页模式
            sql += Sql.NEWSBYREGIONIDS_2;
        }
        sql += Sql.NEWSBYREGIONIDS_3;

        Map<String, Object> parm = new HashMap<>();

        parm.put("currentNewsId", currentNewsId);
        parm.put("lang", lang);
        parm.put("pageSize", pageSize);
        return namedParameterJdbcTemplate.query(dbShardHelper.shardReleaseSql(sql), // 最新shard
                parm, newsRM);
    }

    /**
     * 查找新闻标签
     *
     * @param id
     * @return
     * @since 0.1.0
     */
    public List<NewsLabel> searchNewLablesById(Long id) {
        /*
         * no need shard
		 */
        return jdbcTemplate.query(Sql.NESLABLEBYID, newsLableRM, id);
    }

    /**
     * 根据新闻ids获取所有的新闻标签
     *
     * @param ids
     * @return
     * @since 0.1.0
     */
    public List<NewsLabelExt> searchNewLablesByIds(Long[] ids) {
        /*
         * no need shard
		 */
        String sql = Sql.NESLABLEBYIDS + " and t2.news_id in (" + UtilJdbc.placeHolder(ids.length) + ")";
        return jdbcTemplate.query(sql, ids, newsLableExRM);
    }

    /**
     * 根据地区id列表和标的物类型分页查找符合条件的新闻列表
     *
     * @param regionIds
     * @param type
     * @param currentNewsId
     * @param pageSize
     * @return
     * @author shimingjun 2016/9/30
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public List<News> searchByTickerType(Integer[] regionIds, Integer type, Long currentNewsId, Integer pageSize,
                                         String lang) {
        if (regionIds == null || regionIds.length == 0) {
            return new ArrayList<News>();
        }
        List<Integer> regids = Arrays.asList(regionIds);

        Map sqlParm = new HashMap();
        sqlParm.put("regionIds", regids);
        sqlParm.put("currentNewsId", currentNewsId);
        sqlParm.put("lang", lang);
        sqlParm.put("tickerType", type);
        sqlParm.put("pageSize", pageSize);

        String sql = Sql.SEARCHBYTICKERTYPECONDITION_1;
        if (currentNewsId != null && currentNewsId > 0) {
            sql = sql + Sql.SEARCHBYTICKERTYPECONDITION_2;
        }
        sql = sql + Sql.SEARCHBYTICKERTYPECONDITION_3;
        return namedParameterJdbcTemplate.query(dbShardHelper.shardReleaseSql(sql), // 查最新shared
                sqlParm, newsRM);
    }

    public List<Integer> searchTickerTypeByNewsId(Long newsId) {
        Integer[] all = new Integer[]{1, 2, 3};// TICKERTYPES
        List<Integer> result = new ArrayList<>(3);
        for (Integer type : all) {
            Integer t = UtilJdbc.queryForObject(jdbcTemplate,
                    dbShardHelper.shardDealSql(Sql.SEARCHBYTICKERTYPEBYNEWSID, newsId), //
                    Integer.class, new Object[]{newsId, type});
            if (t != null)
                result.add(type);
        }
        return result;
    }

    public News getByUrlMd5(String urlMd5) {
        StringBuilder sql = new StringBuilder();
        List<String> allSuffixs = dbShardHelper.allNewsSuffix();
        for (int i = 0; i < allSuffixs.size(); i++) {
            if (i > 0)
                sql.append(" union all ");
            sql.append("(" + dbShardHelper.shardDealSql(Sql.GET_BYMD5, allSuffixs.get(i)) + ")");
        }
        Map<String, String> paramMap = new HashMap<>(2, 1);
        paramMap.put("urlMd5", urlMd5);
        List<News> news = namedParameterJdbcTemplate.query(sql.toString(), paramMap, newsRM);
        return (news != null && !news.isEmpty()) ? news.get(0) : null;
    }

	private final static String TimeScope = " and n.news_time >= :startTime and  n.news_time < :endTime ";
	public News getBySumMd5(String sumMd5, Integer tickerId, Date time) {
		Map<String, Object> parm = new HashMap<>();
		parm.put("sumMd5", sumMd5);
		parm.put("tickerId", tickerId);
		if (time != null) {
			parm.put("startTime", DateUtils.addDays(time, -2));
			parm.put("endTime", DateUtils.addDays(time, 2));
		}
		String scope = time == null ? " " : TimeScope;
		final String sql = StringUtils.replaceOnce(Sql.GETBYSUMMD5ANDTICKER, "{timeScope}", scope).intern();
		return Optional
                .ofNullable(
						UtilJdbc.queryForObject(namedParameterJdbcTemplate, dbShardHelper.shardReleaseSql(sql), parm,
								newsRM))
				.orElse(Optional.ofNullable(dbShardHelper.prevSuffixByCurrent())
						.map(preSuffix -> UtilJdbc
						.queryForObject(namedParameterJdbcTemplate,
										dbShardHelper.shardDealSql(sql, preSuffix), parm, newsRM))
                        .orElse(null) );
    }

	public News getBySumMd5_c(String sumMd5, Integer targetId, Date time) {

		Map<String, Object> parm = new HashMap<>();
		parm.put("sumMd5", sumMd5);
		parm.put("targetId", targetId);
		if (time != null) {
			parm.put("startTime", DateUtils.addDays(time, -2));
			parm.put("endTime", DateUtils.addDays(time, 2));
		}
		String scope = time == null ? " " : TimeScope;
		final String sql = StringUtils.replaceOnce(Sql.GETBYSUMMD5ANDCURECY, "{timeScope}", scope).intern();
		return Optional
                .ofNullable(
						UtilJdbc.queryForObject(namedParameterJdbcTemplate, dbShardHelper.shardReleaseSql(sql), parm,
								newsRM))
				.orElse(Optional.ofNullable(dbShardHelper.prevSuffixByCurrent())
						.map(preSuffix -> UtilJdbc.queryForObject(namedParameterJdbcTemplate,
								dbShardHelper.shardDealSql(sql, preSuffix), parm, newsRM))
						.orElse(null));
    }

	public News getBySumMd5_f(String sumMd5, Integer targetId, Date time) {

		Map<String, Object> parm = new HashMap<>();
		parm.put("sumMd5", sumMd5);
		parm.put("targetId", targetId);
		if (time != null) {
			parm.put("startTime", DateUtils.addDays(time, -2));
			parm.put("endTime", DateUtils.addDays(time, 2));
		}
		String scope = time == null ? " " : TimeScope;
		final String sql = StringUtils.replaceOnce(Sql.GETBYSUMMD5ANDFURCAT, "{timeScope}", scope).intern();
		return Optional
				.ofNullable(UtilJdbc.queryForObject(namedParameterJdbcTemplate, dbShardHelper.shardReleaseSql(sql),
						parm, newsRM))
				.orElse(Optional.ofNullable(dbShardHelper.prevSuffixByCurrent())
						.map(preSuffix -> UtilJdbc.queryForObject(namedParameterJdbcTemplate,
								dbShardHelper.shardDealSql(sql, preSuffix), parm, newsRM))
						.orElse(null));

    }

	public News getBySumMd5_h(String sumMd5, Date time) {

		Map<String, Object> parm = new HashMap<>();
		parm.put("sumMd5", sumMd5);
		if (time != null) {
			parm.put("startTime", DateUtils.addDays(time, -2));
			parm.put("endTime", DateUtils.addDays(time, 2));
		}
		String scope = time == null ? " " : TimeScope;
		final String sql = StringUtils.replaceOnce(Sql.GETBYSUMMD5ANDFURHOME, "{timeScope}", scope).intern();
		return Optional
				.ofNullable(UtilJdbc.queryForObject(namedParameterJdbcTemplate, dbShardHelper.shardReleaseSql(sql),
						parm, newsRM))
				.orElse(Optional.ofNullable(dbShardHelper.prevSuffixByCurrent())
						.map(preSuffix -> UtilJdbc.queryForObject(namedParameterJdbcTemplate,
								dbShardHelper.shardDealSql(sql, preSuffix), parm, newsRM))
						.orElse(null));
	}

    @SuppressWarnings({"rawtypes", "unchecked"})
    public List<News> searchNewsByParm(NewsParam input) {
        List parm = new ArrayList();
        String sql_query = Sql.SEARCHBYPARM_1;
        String sql_filter = " where 1=1 and t.del=0 ";
        if (input.getUrl() != null) {
            sql_filter += " and t.url = ? ";
            parm.add(input.getUrl());
        }

        if (input.getTitle() != null && !StringUtils.isBlank(input.getTitle())) {
            sql_filter += " and t.title like +'%" + input.getTitle().trim() + "%'";
        }

        if (input.getLanguage() != null && !StringUtils.isBlank(input.getLanguage())) {
            sql_filter += " and t.language =? ";
            parm.add(input.getLanguage());
        }

        if (input.getNewsTime() != null && !StringUtils.isBlank(input.getLanguage())) {
            sql_filter += " and date(t.news_time) = '" + input.getNewsTime() + "' ";
        }

        if (input.getLabelId() != null && input.getLabelId() > 0) {
            sql_query += ", wli_news_label_rel t2 ";
            sql_filter += " and t2.news_id = t.id and t2.del = 0 and t2.label_id = ? ";
            parm.add(input.getLabelId());
        }

        if (input.getTickerId() != null && input.getTickerId() > 0) {
            sql_query += ", wli_news_ticker_rel t3 ";
            sql_filter += " and t3.news_id = t.id and t3.del = 0 and t3.ticker_id = ? ";
            parm.add(input.getTickerId());
        }
        if (input.getRegionIds() != null && input.getRegionIds().length > 0) {
            sql_filter += " and t.region_id in (" + UtilJdbc.placeHolder(input.getRegionIds().length) + ") ";
            for (int i = 0; i < input.getRegionIds().length; i++) {
                parm.add(input.getRegionIds()[i]);
            }
        }
        sql_filter += "order by t.news_time desc ";
        if (input.getPageIndex() != null && input.getPageSize() != null) {
            sql_filter += " limit ?, ?";
            parm.add(input.getStart());
            parm.add(input.getEndRow());
        }
        logger.trace("sql={}, label={}", sql_query + sql_filter, ToStringBuilder.reflectionToString(input));
        String suffix = Optional.ofNullable(input.getNewsSuffix()).orElse(dbShardHelper.currentSuffix());// 默认最新shard
        return jdbcTemplate.query(dbShardHelper.shardDealSql(sql_query + sql_filter, suffix), parm.toArray(), newsRM);
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Integer countNewsByParm(NewsParam input) {
        List parm = new ArrayList();
        String sql_query = Sql.COUNTBYPARM_1;
        String sql_filter = " where 1=1 and t.del=0 ";

        if (input.getUrl() != null) {
            sql_filter += " and t.url = ? ";
            parm.add(input.getUrl());
        }

        if (input.getTitle() != null && !StringUtils.isBlank(input.getTitle())) {
            sql_filter += " and t.title like +'%" + input.getTitle().trim() + "%'";
        }
        if (input.getLanguage() != null && !StringUtils.isBlank(input.getLanguage())) {
            sql_filter += " and t.language =? ";
            parm.add(input.getLanguage());
        }

        if (input.getNewsTime() != null && !StringUtils.isBlank(input.getLanguage())) {
            sql_filter += " and date(t.news_time) = '" + input.getNewsTime() + "' ";
        }

        if (input.getLabelId() != null && input.getLabelId() > 0) {
            sql_query += ", wli_news_label_rel t2 ";
            sql_filter += " and t2.news_id = t.id and t2.del = 0 and t2.label_id = ? ";
            parm.add(input.getLabelId());
        }
        if (input.getTickerId() != null && input.getTickerId() > 0) {
            sql_query += ", wli_news_ticker_rel t3 ";
            sql_filter += " and t3.news_id = t.id and t3.del = 0 and t3.ticker_id = ? ";
            parm.add(input.getTickerId());
        }
        if (input.getRegionIds() != null && input.getRegionIds().length > 0) {
            sql_filter += " and t.region_id in (" + UtilJdbc.placeHolder(input.getRegionIds().length) + ") ";
            for (int i = 0; i < input.getRegionIds().length; i++) {
                parm.add(input.getRegionIds()[i]);
            }
        }

        logger.trace("sql={}, label={}", sql_query + sql_filter, ToStringBuilder.reflectionToString(input));
        String suffix = Optional.ofNullable(input.getNewsSuffix()).orElse(dbShardHelper.currentSuffix());// 默认最新shard
        return UtilJdbc.queryForObject(jdbcTemplate, dbShardHelper.shardDealSql(sql_query + sql_filter, suffix), //
                Integer.class, parm.toArray());
    }

    /**
     * 更新新闻
     *
     * @param news
     * @since 0.1.0
     */
    public void update(News news) {
        News NewsDb = getById(news.getId());
        namedParameterJdbcTemplate.update(dbShardHelper.shardDealSql(Sql.UPDATE, news.getId()), //
                JdbcHelper.getSqlParameterSource(news));

//        if (!news.compareIndex(NewsDb)) {
        NewsUpdateEvent event = NewsUpdateEvent.produceInstance(news.getId());
        event.setForceReplace(true);
        messageSender.sendModify(event);
//        }
    }

    /**
     * 分页查询news,provide to sync
     *
     * @param lastId
     * @param pageSize
     * @return
     * @since 0.1.0
     */
    public List<News> queryPaging(Long lastId, Integer pageSize) {
        Object[] parms = new Object[2];

        List<News> result = new ArrayList<>();
        parms[0] = lastId;
        parms[1] = pageSize;
        Long shardPoint = lastId + 1;
        List<News> tmp = jdbcTemplate.query(dbShardHelper.shardDealSql(Sql.QUERYPAGING, shardPoint), parms, newsRM);
        if (tmp != null && !tmp.isEmpty()) {// 判断是不是真的last了
            lastId = tmp.get(tmp.size() - 1).getId();
            result.addAll(tmp);
        }
		/*
		 * 最多跨两个shard
		 */
        if (result.size() < pageSize) {
            parms[0] = lastId;
            parms[1] = pageSize - result.size();
            try {
                Optional.ofNullable(dbShardHelper.nextSuffixByNewsId(shardPoint))//
                        .ifPresent(suffix -> {
                            List<News> tmp2 = jdbcTemplate.query(dbShardHelper.shardDealSql(Sql.QUERYPAGING, suffix), //
                                    parms, newsRM);
                            if (tmp2 != null && !tmp2.isEmpty()) {// 判断是不是真的last了
                                result.addAll(tmp2);
                            }
                        });
            } catch (Exception e) {
                logger.warn("queryPaging nextShardDealSql error:{},parms:{}", Arrays.toString(parms));
            }
        }
        return result;
    }

    /**
     * 根据新闻id查询关联的标的物
     *
     * @param id
     * @return
     * @throws ApiException
     * @date 2016年8月19日 上午11:35:22
     */
    public List<Integer> getTickerIdByNewId(Integer id) {
        return jdbcTemplate.queryForList(dbShardHelper.shardDealSql(Sql.GETTICKERIDBYNEWID, id.longValue()), //
                new Object[]{id}, Integer.class);
    }

    /**
     * 根据新闻id查询关联的货币
     *
     * @param id
     * @return
     * @throws ApiException
     * @date 2016年8月19日 上午11:35:22
     */
    public List<Integer> searchCurrencyIdByNewId(Integer id) {
		/*
		 * currency no sharding
		 */
        return jdbcTemplate.queryForList(Sql.SEARCHCURRENCYIDBYNEWSID, new Object[]{id}, Integer.class);
    }

    /**
     * 根据新闻id查询关联的期货群组
     *
     * @param id
     * @return
     * @throws ApiException
     * @date 2016年8月19日 上午11:35:22
     */
    public List<Integer> searchFutrueGroupIdByNewId(Integer id) {
        return jdbcTemplate.queryForList(dbShardHelper.shardDealSql(Sql.SEARCHFUTUREGRUPIDBYNEWSID, id.longValue()), //
                new Object[]{id}, Integer.class);
    }

    /**
     * 根据货币Id获取关联新闻ID列表(提供分页功能，根据最后坐标,依次往后返回指定数量数据)
     *
     * @param currencyId
     * @param currentNewsId
     * @param pageSize
     * @return
     * @author shimingjun
     * @since 0.1.0
     */

    @SuppressWarnings({"unchecked", "rawtypes"})
    public List<News> searchByCurrencyId(Integer[] currencyId, Long currentNewsId, Integer pageSize, String lang) {
        List parm = new ArrayList(5);
        String sq_tickerIds = "";
        for (int i = 0; i < currencyId.length; i++) {
            sq_tickerIds += currencyId[i] + ",";
        }
        sq_tickerIds = StringUtils.substringBeforeLast(sq_tickerIds, ",");
        String sql = StringUtils.replaceOnce(Sql.NEWSBYCURRENCYID_1, "$", sq_tickerIds);
        parm.add(lang);
        if (currentNewsId != null && currentNewsId > 0) {// 开启分页模式
            sql += Sql.NEWSBYCURRENCYID_2;
            parm.add(currentNewsId);
            parm.add(currentNewsId);
            parm.add(currentNewsId);
        }
        sql += " group by t.id";
        sql += Sql.NEWSBYCURRENCYID_3;
        parm.add(0);
        parm.add(pageSize);
        return jdbcTemplate.query(dbShardHelper.shardReleaseSql(sql), //
                parm.toArray(), newsRM);
    }

    public List<News> searchByFuturesId(Integer groupsId, Long currentNewsId, Integer pageSize, String lang) {
        Map<String, Object> parm = new HashMap<>();

        String sql = Sql.NEWSBYFuture_1;
        parm.put("lang", lang);
        parm.put("futureCatId", groupsId);
        if (currentNewsId != null && currentNewsId > 0) {// 开启分页模式
            sql += Sql.NEWSBYFuture_2;
            parm.put("currentId", currentNewsId);
        }
        sql += Sql.NEWSBYFuture_3;
        parm.put("pageSize", pageSize);
        return namedParameterJdbcTemplate.query(dbShardHelper.shardReleaseSql(sql), //
                parm, newsRM);
    }

    public List<Integer> searchRelTickerIds(Long newsId) {
        return jdbcTemplate.queryForList(dbShardHelper.shardDealSql(Sql.searchBYNewsID, newsId), //
                Integer.class, newsId);
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Integer langChainRegionScreen(String lang) {
        Map parm = new HashMap();
        parm.put("lang", lang);
        return namedParameterJdbcTemplate.queryForObject(dbShardHelper.shardReleaseSql(Sql.langChain_1), //
                parm, Integer.class);
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Integer langChainTickerScreen(List<Integer> tickerIds, String lang) {
        Map parm = new HashMap();
        parm.put("tickerIds", tickerIds);
        parm.put("lang", lang);
        return namedParameterJdbcTemplate.queryForObject(dbShardHelper.shardReleaseSql(Sql.langChain_2), //
                parm, Integer.class);
    }

    public Integer langChainFutrueScreen(Integer FutrueId0, String lang) {
        return jdbcTemplate.queryForObject(dbShardHelper.shardReleaseSql(Sql.langChain_3), //
                Integer.class, FutrueId0, lang);
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Integer langChainCurrencyScreen(List<Integer> currencyIds, String lang) {
        Map parm = new HashMap();
        parm.put("currencyIds", currencyIds);
        parm.put("lang", lang);
        return namedParameterJdbcTemplate.queryForObject(dbShardHelper.shardReleaseSql(Sql.langChain_4), //
                parm, Integer.class);
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Integer langChainTickerTypeScreen(List<Integer> regionIds, Integer tickerType, String lang) {
        Map parm = new HashMap();
        parm.put("regionIds", regionIds);
        parm.put("lang", lang);
        parm.put("tickerType", tickerType);
        return namedParameterJdbcTemplate.queryForObject(dbShardHelper.shardReleaseSql(Sql.langChain_5), //
                parm, Integer.class);
    }

    private class Sql {

        private static final String langChain_1 = "select max(1) from wli_news n where n.del = 0 and n.home_page =1 and n.language = :lang  limit 1";
        private static final String langChain_2 = "select max(1) from wli_news n,wli_news_ticker_rel l where n.del = 0 and l.del = 0 and n.id = l.news_id and l.ticker_id in (:tickerIds) and n.language = :lang limit 1";
        private static final String langChain_3 = "select max(1) from wli_news n,wli_news_future_cat_rel l where n.del = 0 and l.del = 0 and n.id = l.news_id and l.future_cat_id = ? and n.language = ? limit 1";
        private static final String langChain_4 = "select max(1) from wli_news n,wli_news_currency_rel l where n.del = 0 and l.del = 0 and n.id = l.news_id and l.currency_id in (:currencyIds) and n.language = :lang limit 1";
        public static final String langChain_5 = "SELECT max(1)  FROM wli_news t1 where t1.del=0  AND t1.region_id IN (:regionIds)  and t1.language = :lang "
                + "and EXISTS (SELECT 1 from wli_news_ticker_rel t2  where t2.news_id = t1.id and t2.del=0 and t2.ticker_type = :tickerType) limit 1";

        private static final String GETBYID = "select * from wli_news where id=?";
        private static final String DELBYID = "update wli_news set del = 1 where id=?";

        private static final String searchBYNewsID = "select ticker_id from wli_news_ticker_rel where news_id=? and del = 0";

        private static final String NEWSBYTICKERID_1 = "SELECT t.id,t.title,t.source_name,collect_source,t.news_time,t.summary,t.url,t.store_format,t.store_shard,t.url_md5 from wli_news t,wli_news_ticker_rel t2 "
                + " where t2.ticker_id = ? " + " and t2.del = 0 " + " and t.del = 0 "
                + " and t.id = t2.news_id and t.language = ?";
        private static final String NEWSBYTICKERIDS_1 = "SELECT t.id,t.title,t.source_name,t.news_time,t.summary,t.url,t.store_format,t.store_shard,t.url_md5 from wli_news t,wli_news_ticker_rel t2 "
                + " where t2.ticker_id in ($) " + " and t2.del = 0 " + " and t.del = 0 "
                + " and t.id = t2.news_id and t.language = ? ";
        private static final String NEWSBYTICKERID_2 = "and ( "
                + "( t.news_time = (SELECT t3.news_time from wli_news t3 where t3.id = ?)  	 and t.id <  ?  )"
                + "    or  t.news_time < (SELECT t3.news_time from wli_news t3 where t3.id = ? )" + "	)";
        private static final String NEWSBYTICKERID_3 = " ORDER BY  t.news_time desc,t.id DESC LIMIT ?";

        private static final String NEWSBYCURRENCYID_1 = "SELECT t.id,t.title,t.source_name,collect_source,t.news_time,t.summary,t.url,t.store_format,t.store_shard,t.url_md5 from wli_news t,wli_news_currency_rel t2 "
                + " where t2.currency_id in ($) " + " and t2.del = 0 " + " and t.del = 0 "
                + " and t.id = t2.news_id and t.language = ?";
        private static final String NEWSBYCURRENCYID_2 = "and ( "
                + "( t.news_time = (SELECT t3.news_time from wli_news t3 where t3.id = ?)  	 and t.id <  ?  )"
                + "    or  t.news_time < (SELECT t3.news_time from wli_news t3 where t3.id = ? )" + "	)";
        private static final String NEWSBYCURRENCYID_3 = " ORDER BY  t.news_time desc,t.id DESC LIMIT ?,?";

        private static final String NEWSBYFuture_1 = "SELECT t.id,t.title,t.source_name,collect_source,t.news_time,t.summary,t.url,t.store_format,t.store_shard,t.url_md5 from wli_news t,wli_news_future_cat_rel t2 "
                + " where t2.future_cat_id = :futureCatId   and t2.del = 0   and t.del = 0   and t.id = t2.news_id and t.language = :lang";
        private static final String NEWSBYFuture_2 = " and ( ( t.news_time = (SELECT t3.news_time from wli_news t3 where t3.id = :currentId) and t.id <  :currentId )"
                + " or  t.news_time < (SELECT t3.news_time from wli_news t3 where t3.id = :currentId )" + "	)";
        private static final String NEWSBYFuture_3 = " ORDER BY  t.news_time desc,t.id DESC LIMIT :pageSize";

        private static final String NEWSBYREGIONIDS_1 = "SELECT id,title,summary,main_pic, collect_source,news_time,source_name,region_id,url,store_format,store_shard,url_md5 FROM	wli_news t "
                + " WHERE   t.language = :lang and t.home_page = 1";
        private static final String NEWSBYREGIONIDS_2 = " and (  ( t.news_time = (SELECT t2.news_time from wli_news t2 where t2.id = :currentNewsId) and t.id <  :currentNewsId  ) "
                + " or  t.news_time < (SELECT t2.news_time from wli_news t2 where t2.id = :currentNewsId ) 	) ";
        private static final String NEWSBYREGIONIDS_3 = " ORDER BY  t.news_time desc,t.id DESC LIMIT 0,:pageSize";

        public static final String NESLABLEBYID = "SELECT t.id , t.name , t.describe , t.region_id, t.create_time, t.update_time, t.del FROM wli_news_label t,wli_news_label_rel t2 WHERE t.del = 0 and t2.del = 0 and t.id = t2.label_id and t2.news_id = ?";

        public static final String NESLABLEBYIDS = "SELECT t2.news_id, t.id , t.name , t.describe , t.region_id, t.create_time, t.update_time, t.del FROM wli_news_label t,wli_news_label_rel t2 WHERE t.del = 0 and t2.del = 0 and t.id = t2.label_id ";

        // 判定该新闻有没有这个tickerType
        public static final String SEARCHBYTICKERTYPEBYNEWSID = "SELECT 1 FROM wli_news_ticker_rel t2 WHERE t2.del = 0   AND t2.news_id = ? AND t2.ticker_type = ? limit 1";

        public static final String SEARCHBYTICKERTYPECONDITION_1 = "SELECT t1.*  FROM wli_news t1 where t1.del=0  AND t1.region_id IN (:regionIds)  and t1.language = :lang "
                + "and EXISTS (SELECT 1 from wli_news_ticker_rel t2   where t2.news_id = t1.id and t2.del=0  and t2.ticker_type = :tickerType)";

        public static final String SEARCHBYTICKERTYPECONDITION_2 = " and  (t1.news_time < (select news_time from wli_news where id = :currentNewsId) "
                + " or (t1.id <  :currentNewsId and t1.news_time < (select news_time from wli_news where id = :currentNewsId))) ";

        public static final String SEARCHBYTICKERTYPECONDITION_3 = " ORDER BY t1.news_time DESC, t1.id DESC LIMIT :pageSize";

        private static final String ADD_TICKER_NEWS = "INSERT INTO  wli_news (id, title, summary,sum_md5,collect_source, store_shard,store_format,main_pic, news_time, source_name, url,language, region_id,url_md5,store_version,home_page,sync_us,site_type) VALUES (:id, :title,:summary,:sumMd5,:collectSource,:storeShard,:storeFormat,:mainPic, :newsTime, :sourceName, :url,:language,:regionId,:urlMd5,:storeVersion,:homePage,:syncUs,:siteType)";

        private static final String GET_BYMD5 = "select * from wli_news where del = 0 and url_md5 = :urlMd5 limit 1";


		private static final String GETBYSUMMD5ANDTICKER = "select n.* from wli_news n where n.del = 0 and n.sum_md5 = :sumMd5 {timeScope} "
				+ " and EXISTS  (select 1 from wli_news_ticker_rel r where r.ticker_id  = :tickerId and r.news_id = n.id)  limit 1";
		private static final String GETBYSUMMD5ANDCURECY = "select n.* from wli_news n where n.del = 0 and n.sum_md5 = :sumMd5 {timeScope} "
				+ " and EXISTS  (select 1 from wli_news_currency_rel r where r.currency_id  = :targetId and r.news_id = n.id)  limit 1";
		private static final String GETBYSUMMD5ANDFURCAT = "select n.* from wli_news n where n.del = 0 and n.sum_md5 = :sumMd5 {timeScope} "
				+ " and EXISTS  (select 1 from wli_news_future_cat_rel r where r.future_cat_id  = :targetId and r.news_id = n.id)  limit 1";
		private static final String GETBYSUMMD5ANDFURHOME = "select n.* from wli_news n where n.del = 0 and n.sum_md5 = :sumMd5 {timeScope} and n.home_page >=1 limit 1";
        public static final String SEARCHBYPARM_1 = "select * from wli_news t";
        public static final String COUNTBYPARM_1 = "select COUNT(1) from wli_news t";

        public static final String UPDATE = "update wli_news set title=ifnull(:title,title),home_page=ifnull(:homePage,home_page),sync_us=ifnull(:syncUs,sync_us),  collect_source=ifnull(:collectSource,collect_source), summary=ifnull(:summary,summary),sum_md5=ifnull(:sumMd5,sum_md5), store_shard=ifnull(:storeShard,store_shard),store_format=ifnull(:storeFormat,store_format),main_pic=ifnull(:mainPic,main_pic),news_time=ifnull(:newsTime,news_time), source_name=ifnull(:sourceName,source_name), url= ifnull(:url,url),language=ifnull(:language,language), region_id=ifnull(:regionId,region_id),url_md5=ifnull(:urlMd5,url_md5), store_version =ifnull(:storeVersion,store_version), site_type =ifnull(:siteType,site_type) where id= :id";

        public static final String QUERYPAGING = "SELECT * FROM	wli_news t  WHERE t.del=0 and  t.id > ? ORDER BY  t.id asc LIMIT ?";

        public static final String GETTICKERIDBYNEWID = "select ticker_id from wli_news_ticker_rel where del = 0 and news_id=? limit 100";
        public static final String SEARCHCURRENCYIDBYNEWSID = "select currency_id from wli_news_currency_rel where del = 0 and news_id=? limit 100";
        public static final String SEARCHFUTUREGRUPIDBYNEWSID = "select future_cat_id from wli_news_future_cat_rel where del = 0 and news_id=? limit 100";

        private static final String exist_TICKER_NEWS_REL = "select count(1) from wli_news_ticker_rel  l where l.news_id = :newsId and l.ticker_id = :tickerId and l.del=0";
        private static final String ADD_TICKER_NEWS_REL = "INSERT INTO  wli_news_ticker_rel (  news_id ,  ticker_id,ticker_type ) values (:newsId, :tickerId,:tickerType)";

        private static final String exist_currency_NEWS_REL = "select count(1) from wli_news_currency_rel  l where l.news_id = :newsId and l.currency_id = :currencyId and l.del=0";
        private static final String ADD_currency_NEWS_REL = "INSERT INTO  wli_news_currency_rel (  news_id ,  currency_id ) values (:newsId,:currencyId)";

        private static final String exist_futureCat_NEWS_REL = "select count(1) from wli_news_future_cat_rel  l where l.news_id = :newsId and l.future_cat_id = :futureCatId and l.del=0";
        private static final String ADD_futureCat_NEWS_REL = "INSERT INTO  wli_news_future_cat_rel (  news_id ,  future_cat_id ) values (:newsId,:futureCatId)";

    }

}
